package com.example.tingsugar.mapper;

import com.example.tingsugar.bean.UserBean;
import org.apache.ibatis.annotations.Select;

import java.util.List;

public interface UserMapper {
    @Select("select \n" +
        "    date,\n" +
        "    sum(new_user_ct) new_user_ct,\n" +
        "    sum(activate_user_ct) activate_user_ct,\n" +
        "    sum(new_vip_ct) new_vip_ct\n" +
        "from\n" +
        "    (SELECT\n" +
        "        #{date} AS date,\n" +
        "        sum(CAST(new_user_ct, 'int')) AS new_user_ct,\n" +
        "        0 activate_user_ct,\n" +
        "        0 new_vip_ct\n" +
        "    FROM dws_user_register_window\n" +
        "    WHERE toYYYYMMDD(stt) = #{date} \n" +
        "    union all\n" +
        "    SELECT\n" +
        "        #{date} AS date,\n" +
        "        0 new_user_ct,\n" +
        "        sum(CAST(active_user_ct, 'int')) AS activate_user_ct,\n" +
        "        0 new_vip_ct\n" +
        "    FROM dws_user_active_window\n" +
        "    WHERE toYYYYMMDD(stt) = #{date}\n" +
        "    union all\n" +
        "    SELECT\n" +
        "        #{date} AS date,\n" +
        "        0 new_user_ct,\n" +
        "        0 activate_user_ct,\n" +
        "        sum(CAST(new_vip_user_ct, 'int')) AS new_vip_ct\n" +
        "    FROM dws_user_vip_window\n" +
        "    WHERE toYYYYMMDD(stt) = #{date}) a\n" +
        "group by date;\n")
    List<UserBean> user(Integer date);
}
